package com.github.data_manage.service

import com.github.data_manage.dao.FundDepthResearchAttachDao
import com.github.data_manage.domain.po.FundDepthResearchAttach
import com.github.data_manage.util.DateTimeUtils.Companion.atEndOfMonth
import com.github.data_manage.util.ExcelUtils
import org.apache.poi.ss.usermodel.Workbook
import org.springframework.stereotype.Component
import org.springframework.util.StringUtils
import java.math.BigDecimal
import java.math.RoundingMode
import kotlin.reflect.KMutableProperty

/**
 *
 * @author chenhainan
 * @since 2025/9/2
 */
@Component
open class FundDepthResearchAttachImportService(
    private val fundDepthResearchAttachDao: FundDepthResearchAttachDao
) {


    private val nonMultiplyField: Set<String> = setOf(
        "fundAumMn",
        "strategyAumMn",
        "firmAumMn",
        "ofLong",
        "ofShort"
    )

    fun loadAndSaveFundDepthResearchAttach(
        fundId: Int, workbook: Workbook,
        nonMatchExcelIndexNames: HashSet<String>
    ): List<FundDepthResearchAttach> {
        val fundDepthResearchAttachList = mutableListOf<FundDepthResearchAttach>()
        val dataSheet = workbook.getSheetAt(0)
        val firstRow = dataSheet.getRow(0)
        val thirdRow = dataSheet.getRow(2)
        val indexNameMap = HashMap<Int, String>(400)
        var lastHeadFirstRowCellValue: String? = ""
        for (index in 1..thirdRow.lastCellNum) {
            val firstRowCellValue =
                firstRow.getCell(index)?.stringCellValue?.trim()?.replace("\n", "") ?: ""
            if (StringUtils.hasText(firstRowCellValue)) {
                lastHeadFirstRowCellValue = firstRowCellValue
            }
            val thirdRowCellValue =
                thirdRow.getCell(index)?.stringCellValue?.trim()?.replace("\n", "") ?: ""
            if (!StringUtils.hasText(lastHeadFirstRowCellValue)) {
                if (!StringUtils.hasText(lastHeadFirstRowCellValue)) {
                    break
                }
                continue
            }
            indexNameMap[index] = "$lastHeadFirstRowCellValue$thirdRowCellValue"
        }
//        nonMatchExcelIndexNames.addAll(indexNameMap.values)

        for (rowNum in 3..dataSheet.lastRowNum) {
            val row = dataSheet.getRow(rowNum)
            val monthCell = row.getCell(0) ?: break
            val monthValue = ExcelUtils.getLocalDateCellValueFromCell(monthCell)?.atEndOfMonth() ?: break
            val fundDepthResearchAttach =
                fundDepthResearchAttachDao.findByFundIdEqualsAndDataMonthEquals(fundId, monthValue)
                    ?: FundDepthResearchAttach(fundId, monthValue)

            for (index in 1..row.lastCellNum) {
                val excelIndexName = indexNameMap[index]
                if (!StringUtils.hasText(excelIndexName)) {
                    continue
                }
                val fieldName = FundDepthResearchAttach.propertyNameMap.getOrDefault(excelIndexName, "")
                if (!StringUtils.hasText(fieldName)) {
                    nonMatchExcelIndexNames.add(excelIndexName!!)
                    continue
                }
                val property = FundDepthResearchAttach.propertyMap[fieldName] ?: continue
                val valueCell = row.getCell(index)
                val formatValue = ExcelUtils.getStringCellValueFromCell(valueCell) ?: continue
                if (StringUtils.hasText(formatValue)) {
                    val actualValue =
                        // 尝试类型转换
                        when (property.returnType.classifier) {
                            String::class -> formatValue as String?
                            // 有百分号的就不乘以 100
                            BigDecimal::class -> if (nonMultiplyField.contains(fieldName)) {
                                valueCell.numericCellValue.toBigDecimal()
                                    .setScale(2, RoundingMode.HALF_UP)
                            } else {
                                BigDecimal(formatValue).multiply(BigDecimal.valueOf(100))
                                    .setScale(2, RoundingMode.HALF_UP)
                            }

                            else -> formatValue
                        }
                    property.setter.call(fundDepthResearchAttach, actualValue)
                }
            }
            fundDepthResearchAttachList.add(fundDepthResearchAttach)
        }

        for (depthResearchAttach in fundDepthResearchAttachList) {
            if (depthResearchAttach.exposureTop5Longs == null) {
                depthResearchAttach.exposureTop5Longs = if (depthResearchAttach.top1LongPositionExposure != null &&
                    depthResearchAttach.top2LongPositionExposure != null &&
                    depthResearchAttach.top3LongPositionExposure != null &&
                    depthResearchAttach.top4LongPositionExposure != null &&
                    depthResearchAttach.top5LongPositionExposure != null
                ) {
                    depthResearchAttach.top1LongPositionExposure!!
                        .add(depthResearchAttach.top2LongPositionExposure)
                        .add(depthResearchAttach.top3LongPositionExposure)
                        .add(depthResearchAttach.top4LongPositionExposure)
                        .add(depthResearchAttach.top5LongPositionExposure)
                } else {
                    BigDecimal.ZERO
                }
            }
            if (depthResearchAttach.exposureTop5Shorts == null) {
                depthResearchAttach.exposureTop5Shorts = if (depthResearchAttach.top1ShortPositionExposure != null &&
                    depthResearchAttach.top2ShortPositionExposure != null &&
                    depthResearchAttach.top3ShortPositionExposure != null &&
                    depthResearchAttach.top4ShortPositionExposure != null &&
                    depthResearchAttach.top5ShortPositionExposure != null
                ) {
                    depthResearchAttach.top1ShortPositionExposure!!
                        .add(depthResearchAttach.top2ShortPositionExposure)
                        .add(depthResearchAttach.top3ShortPositionExposure)
                        .add(depthResearchAttach.top4ShortPositionExposure)
                        .add(depthResearchAttach.top5ShortPositionExposure)
                } else {
                    BigDecimal.ZERO
                }
            }
            if (depthResearchAttach.exposureTop10Longs == null) {
                depthResearchAttach.exposureTop10Longs = if (depthResearchAttach.top1LongPositionExposure != null &&
                    depthResearchAttach.top2LongPositionExposure != null &&
                    depthResearchAttach.top3LongPositionExposure != null &&
                    depthResearchAttach.top4LongPositionExposure != null &&
                    depthResearchAttach.top5LongPositionExposure != null &&
                    depthResearchAttach.top6LongPositionExposure != null &&
                    depthResearchAttach.top7LongPositionExposure != null &&
                    depthResearchAttach.top8LongPositionExposure != null &&
                    depthResearchAttach.top9LongPositionExposure != null &&
                    depthResearchAttach.top10LongPositionExposure != null
                ) {
                    depthResearchAttach.top1LongPositionExposure!!
                        .add(depthResearchAttach.top2LongPositionExposure)
                        .add(depthResearchAttach.top3LongPositionExposure)
                        .add(depthResearchAttach.top4LongPositionExposure)
                        .add(depthResearchAttach.top5LongPositionExposure)
                        .add(depthResearchAttach.top6LongPositionExposure)
                        .add(depthResearchAttach.top7LongPositionExposure)
                        .add(depthResearchAttach.top8LongPositionExposure)
                        .add(depthResearchAttach.top9LongPositionExposure)
                        .add(depthResearchAttach.top10LongPositionExposure)
                } else {
                    BigDecimal.ZERO
                }
            }
            if (depthResearchAttach.exposureTop10Shorts == null) {
                depthResearchAttach.exposureTop10Shorts = if (depthResearchAttach.top1ShortPositionExposure != null &&
                    depthResearchAttach.top2ShortPositionExposure != null &&
                    depthResearchAttach.top3ShortPositionExposure != null &&
                    depthResearchAttach.top4ShortPositionExposure != null &&
                    depthResearchAttach.top5ShortPositionExposure != null &&
                    depthResearchAttach.top6ShortPositionExposure != null &&
                    depthResearchAttach.top7ShortPositionExposure != null &&
                    depthResearchAttach.top8ShortPositionExposure != null &&
                    depthResearchAttach.top9ShortPositionExposure != null &&
                    depthResearchAttach.top10ShortPositionExposure != null
                ) {
                    depthResearchAttach.top1ShortPositionExposure!!
                        .add(depthResearchAttach.top2ShortPositionExposure)
                        .add(depthResearchAttach.top3ShortPositionExposure)
                        .add(depthResearchAttach.top4ShortPositionExposure)
                        .add(depthResearchAttach.top5ShortPositionExposure)
                        .add(depthResearchAttach.top6ShortPositionExposure)
                        .add(depthResearchAttach.top7ShortPositionExposure)
                        .add(depthResearchAttach.top8ShortPositionExposure)
                        .add(depthResearchAttach.top9ShortPositionExposure)
                        .add(depthResearchAttach.top10ShortPositionExposure)
                } else {
                    BigDecimal.ZERO
                }
            }
        }

        return fundDepthResearchAttachList
    }

}